A Workflow Demonstration

This script will present several new components of the Nonprofit Open Data Collective data environment using an example motivated by the “Glass Cliff”, or the idea the women are more likely to be appointed to precarious positions of power relative to their male counterparts.

We specifically want to highlight the value of an integrated data environment developed around the EFILE Database, one of the emerging research centerpieces in nonprofit scholarship. This tutorial demonstrates a reproducible data engineering workflow that follows FAIR Data Guidelines. We will answer the research question using a dataset created through the following 8 Steps:

  1. Build tables using the irs990efile package.
  2. Standardize names of the leadership team present in Part VII (the compensation tables) using the peopleparser package.
  3. Standardize titles in Part VII using the titleclassifier package.
  4. Identify CEO transition years within the data.
  5. Build a financial dataset by combining the following 990 Parts: revenues, expenses, and assets.
  6. Generate common financial operating ratios using the fiscal package.
  7. Add NTEE codes and standardized address fields from the Unified BMF.
  8. Merge financial tables and compensation tables into a single table for the analysis.

We are using the term “integrated data environment” here to mean:

These things allow us to create more expressive and intuitive data steps that anyone can replicate. It also allows researchers to create custom tools for refining or analyzing data, as demonstrated by the packages used in this workflow.

Glass Cliff Research

The glass cliff is a hypothesized phenomenon in which women are more likely to break the “glass ceiling” (i.e. achieve leadership roles in business and government) during periods of crisis or downturn when the risk of failure is highest.

We know from previous studies that male CEOs are put in charge of large, thriving nonprofits while women are more likely to be hired to lead smaller social services organizations.

Here we explore the question of whether female CEOs are more likely to be hired when the current leader is struggling.

Grasse, N. J., Heidbreder, B., Kukla-Acevedo, S. A., & Lecy, J. D. (2024). Some Good News, More Bad News: Two Decades of the Gender Pay Gap for Nonprofit Directors and Chief Financial Officers. Review of Public Personnel Administration, 0734371X241248854.

Data and Software

Efile Data

Processed IRS 990 Efile data are housed in the data catalog with the respective data dictionaries. You can find further information about sources, processing, and final parsing on these sites .

R Packages

We will be using the following NODC packages for the demo:

devtools::install_github( 'Nonprofit-Open-Data-Collective/peopleparser' )
devtools::install_github( 'nonprofit-open-data-collective/titleclassifier' )
devtools::install_github( 'nonprofit-open-data-collective/fiscal')
library( tidyverse )
library( pander )

# nonprofit data packages 
library( peopleparser )
library( titleclassifier )
library( fiscal )

# helper functions for the demo: 
nodc <- "https://raw.githubusercontent.com/Nonprofit-Open-Data-Collective/"
repo <- "arnova-2024/refs/heads/main/"
file <- "functions.R"
source( paste0( nodc, repo, file ) )

Step 1: Building Efile Tables from Scratch

This step builds Efile tables by loading raw XML efile returns from the Data Commons and using the irs990efile package to parse XML files into rectangular CSV tables.

# Create sample of 100 
# organizations from 2018
# library( irs990efile )

index <- build_index( tax.years=2018 )

index100 <-
  index %>% 
  filter( FormType %in% c("990","990EZ") ) %>%
  sample_n( 100 )

TABLES <- c( "F9-P00-T00-HEADER",
             "F9-P01-T00-SUMMARY",
             "F9-P08-T00-REVENUE",
             "F9-P09-T00-EXPENSES",
             "F9-P11-T00-ASSETS" )

URLS <- index100$URL

build_tables( urls=URLS, year=2018, table.names=TABLES  )

Typically you would not need to replicate this step since it is a computationally-intensive process. It can take a couple of days to build the full efile database. It is much easier to pull existing CSV files from NCCS: EFILE DATA CATALOG.

Step 2: Parse DTK Names

root    <- paste0( nodc, repo )
fn      <- "data/PART-VII-SAMPLE-10.CSV"
url     <- paste0( root, fn )
partvii <- read.csv( url )
Table continues below
NAME TAXYR FORMTYPE F9_07_COMP_DTK_NAME_PERS
President and Fellows of Middlebury College 2009 990 Ronald Liebowitz
President and Fellows of Middlebury College 2009 990 Frederick M Fritz
President and Fellows of Middlebury College 2009 990 Marna C Whittington
President and Fellows of Middlebury College 2009 990 Kendrick R Wilson III
President and Fellows of Middlebury College 2009 990 S Carolyn Ramos
President and Fellows of Middlebury College 2009 990 Roxanne M Leighton
F9_07_COMP_DTK_TITLE F9_07_COMP_DTK_AVE_HOUR_WEEK F9_07_COMP_DTK_COMP_ORG
President 40 409609
Chair 5 0
Vice Chair 5 0
Vice Chair 5 0
Alumni Trustee 1 0
Charter Trustee 1 0

Now let’s use peopleparser to clean the names. Names do not arrive standardized in any specific format:

## Karen A Jackson  ;;  FERDINAND JONES  ;;  McCarron Edward
## BRUCE STEDMAN  ;;  BETH PANELLA  ;;  SUSAN MCNAMARA
## NICHOLAS LASOFF  ;;  Jon Lumbra  ;;  A HERBERT SCHWARTZ MD
## JOSEPH BATOR  ;;  RITA WARNOCK  ;;  PAUL WASSON
## STELLA CHAN  ;;  John McCardell  ;;  ELLEN KINLIN

The peopleparser package will remove nuisance text and split the name string into 5 parts:

And add a predicted gender label (M/F/U) plus confidence level once it is able to identify the individual’s first name:

peopleparser::parse.name( "(1) Rev Kendrick R Wilson III" )
## [1] "REV|KENDRICK|R|WILSON|III||M|100"
peopleparser::parse.name( "Doctor Wilson III, Kendrick R" )
## [1] "DOCTOR|KENDRICK|R|WILSON|III||M|100"
# gender prediction is based on first names
peopleparser::parse.name( "Wilson, K R Until June 2008" )
## [1] "|K|R|WILSON|||U|0.0"
nm <- partvii[[ "F9_07_COMP_DTK_NAME_PERS" ]] %>% unique()
nm.parsed <- peopleparser::parse.names( nm )
Table continues below
name salutation first_name middle_name last_name
Ronald Liebowitz RONALD LIEBOWITZ
Frederick M Fritz FREDERICK M FRITZ
Marna C Whittington MARNA C WHITTINGTON
Kendrick R Wilson III KENDRICK R WILSON
S Carolyn Ramos S CAROLYN RAMOS
Roxanne M Leighton ROXANNE M LEIGHTON
suffix status gender gender_confidence
M 99.7
M 100
F 100
III M 100
F 100
F 100

Join parsed names back to the original data frame.

# CORE R VERSION OF A TABLE JOIN 
partvii <- 
  partvii %>% 
  merge( nm.parsed, 
         by.x="F9_07_COMP_DTK_NAME_PERS", by.y="name", 
         all.x=T )
# TIDYVERSE VERSION OF A TABLE JOIN
partvii <- 
  partvii %>% 
  left_join( nm.parsed, 
             by=c( "F9_07_COMP_DTK_NAME_PERS" = "name" ) )
write.csv( partvii, "data/PART-VII-SAMPLE-10-PARSED-NAMES.CSV", row.names=F )

Step 3: Standardize Titles

root    <- paste0( nodc, repo )
fn      <- "data/PART-VII-SAMPLE-10-PARSED-NAMES.CSV"
url     <- paste0( root, fn )
partvii <- read.csv( url )
# steps from titleclassifier package 

titles <-  
  partvii %>% 
  standardize_df() %>% 
  remove_dates() %>% 
  standardize_conj() %>% 
  split_titles() %>% 
  standardize_spelling() %>% 
  gen_status_codes() %>% 
  standardize_titles() %>%
  categorize_titles()
## ✔ standardize df step complete
## ✔ remove dates step complete
## ✔ standardize conjunctions step complete
## ✔ split titles step complete
## ✔ standardize spelling step complete
## ✔ generate status codes step complete
## ✔ standardize titles step complete
## ✔ categorize titles step complete
dtk.name title.raw title.mult.x title.order
KATHRYN DUPREE EXECUTIVE DIRECTOR 0 1
LINDA GRIMM ACADEMY DIRECTOR 0 1
ARLENE KAYE DIRECTOR OF CHILDREN’S BEHAVIORAL SERVICES 0 1
TACIE LOWE DIRECTOR OF INDIVIDUAL & FAMILY SUPPORT 0 1
CARL J CASPER CHAIR 0 1
THOMAS IGOE TREASURER/SECRETARY 1 2
THOMAS IGOE TREASURER/SECRETARY 1 1
LARRY WOOD DIRECTOR 0 1
STEPHEN SIMONSON RESIDENTIAL DIRECTOR 0 1
ARLENE KAYE CBD DIRECTOR 0 1

Title processing steps from the package:

Table continues below
  title.raw title.v4
150 EXECUTIVE DIRECTOR/FSE DIRECTOR EXECUTIVE DIRECTOR
151 EXECUTIVE DIRECTOR/FSE DIRECTOR FSE DIRECTOR
152 DIRECTOR DIRECTOR
153 DIRECTOR DIRECTOR
154 DIRECTOR DIRECTOR
155 DIRECTOR, FLUTIE FDN. AND FSE DIRECTOR AND FLUTIE FDN AND FSE
156 CO-CHAIR/DIRECTOR CO-CHAIR
157 DIRECTOR DIRECTOR
158 CO-CHAIR/DIRECTOR DIRECTOR
159 DIRECTOR DIRECTOR
160 DIRECTOR DIRECTOR
161 DIRECTOR DIRECTOR
162 EXECUTIVE DIRECTOR EXECUTIVE DIRECTOR
163 CLERK/DIRECTOR CLERK
164 CLERK/DIRECTOR DIRECTOR
165 DIRECTOR DIRECTOR
  title.v7 title.standard
150 CEO CEO
151 FSE DIRECTOR NA
152 DIRECTOR DIRECTOR
153 DIRECTOR DIRECTOR
154 DIRECTOR DIRECTOR
155 DIRECTOR AND FLUTIE FDN AND FSE NA
156 CO-CHAIR BOARD PRESIDENT
157 DIRECTOR DIRECTOR
158 DIRECTOR DIRECTOR
159 DIRECTOR DIRECTOR
160 DIRECTOR DIRECTOR
161 DIRECTOR DIRECTOR
162 CEO CEO
163 CLERK CLERK
164 DIRECTOR DIRECTOR
165 DIRECTOR DIRECTOR

Pay and hours tabulated relative to other employees in the same org:

Table continues below
dtk.name title.standard tot.hours hours.rank tot.comp pay.max
KATHRYN DUPREE CEO 37.5 1 188130 188130
LINDA GRIMM NA 37.5 1 179286 188130
ARLENE KAYE NA 37.5 1 135385 188130
TACIE LOWE NA 37.5 1 118760 188130
CARL J CASPER BOARD PRESIDENT 0.5 2 0 188130
THOMAS IGOE BOARD SECRETARY 0.5 2 0 188130
THOMAS IGOE BOARD TREASURER 0.5 2 0 188130
LARRY WOOD DIRECTOR 0.5 2 0 188130
pay.rank
1
2
14
20
25
25
25
25
Table continues below
  dtk.name title.standard tot.hours
9 STEPHEN SIMONSON NA 37.5
10 ARLENE KAYE NA 37.5
11 TONIA HOLLAND CHIEF FINANCIAL OFFICER 37.5
12 ALEXANDER WESTPHAL MD DIRECTOR 0.5
13 KEVIN DALY DIRECTOR 0.5
14 WICK CHAMBERS DIRECTOR 0.5
  hours.rank tot.comp pay.max pay.rank
9 1 133080 188130 15
10 1 121476 188130 17
11 1 111146 188130 22
12 2 0 188130 25
13 2 0 188130 25
14 2 0 188130 25
Table continues below
  dtk.name title.standard tot.hours
15 KATHRYN DUPEE CEO 37.5
16 JOHN BALDINO DIRECTOR OF FACILITIES 37.5
17 JOSEPH SPITERI GENERAL MANAGER 37.5
18 TACIE LOWE NA 37.5
19 THOMAS IGOE BOARD TREASURER 0.5
20 KARA FARACLAS BOARD VICE PRESIDENT 0.5
21 JAMES MCPARTLAND PHD DIRECTOR 0.5
22 JANETTE JOHNSON DIRECTOR 0.5
23 STEPHEN WIZNER DIRECTOR 0.5
  hours.rank tot.comp pay.max pay.rank
15 1 176077 188130 3
16 1 141983 188130 7
17 1 141538 188130 8
18 1 120327 188130 18
19 2 0 188130 25
20 2 0 188130 25
21 2 0 188130 25
22 2 0 188130 25
23 2 0 188130 25 
partvii <- 
  merge( partvii, titles, 
         by.x=c("EIN","TAXYR"), by.y=c("ein","taxyr"),
         all.x=TRUE )
write.csv( partvii, "data/PART-VII-SAMPLE-10-PARSED-NAMES-TITLES.CSV", row.names=F )

Step 4: Identify CEO Transitions

We are omitting this step to avoid an overly complex example.

Step 5: Compile Financials

We will first build a financials table by selecting the relevant 990 parts, then combining them:

##   JOINING ONE TO ONE TABLES

root <- paste0( nodc, repo )

fn1  <- "data/F9-P00-T00-HEADER-SAMPLE-10.CSV"
fn2  <- "data/F9-P01-T00-SUMMARY-SAMPLE-10.CSV"
fn3  <- "data/F9-P08-T00-REVENUE-SAMPLE-10.CSV"
fn4  <- "data/F9-P09-T00-EXPENSES-SAMPLE-10.CSV"
fn5  <- "data/F9-P10-T00-BALANCE-SHEET-SAMPLE-10.CSV"

d1 <- read.csv( paste0( root, fn1 )  )
d2 <- read.csv( paste0( root, fn2 )  )
d3 <- read.csv( paste0( root, fn3 )  )
d4 <- read.csv( paste0( root, fn4 )  )
d5 <- read.csv( paste0( root, fn5 )  )

The efile one-to-one tables all share the same IDs:

intersect( names(d1), names(d2) )
## [1] "OBJECTID"       "URL"            "RETURN_VERSION" "ORG_EIN"       
## [5] "ORG_NAME_L1"    "ORG_NAME_L2"    "RETURN_TYPE"    "TAX_YEAR"      
## [9] "EIN2"

Which makes merging files easy:

df <- merge( d1, d2 )
df <- merge( df, d3 )
df <- merge( df, d4 )
df <- merge( df, d5 )
write.csv( df, "data/FINANCIALS.CSV" )

Note that dataset dimensions should not change for one-to-one merges:

dim(d1)
## [1] 107  55
dim(d2)
## [1] 107  50
dim(df)
## [1] 107 376

See the appendix for the code used to compile the five tables above for the sample.

Step 6: Add Financial Operating Ratios

root <- paste0( nodc, repo )
fn   <- "data/FINANCIALS.CSV"
df   <- read.csv( paste0( root, fn )  )
F9_01_EXP_TOT_PY F9_01_EXP_REV_LESS_EXP_CY F9_01_EXP_REV_LESS_EXP_PY
3217608 56922 31606
870793 -194147 -257955
692598 42635 31852
11765651 519017 403144
8708339 -558773 -308774

The fiscal package contains the following financial ratios:

df <- get_aer( df )     #   Assets to Revenues Ratio
df <- get_arr( df )     #   Assets to Revenues Ratio
df <- get_cr( df )      #   Current Ratio
df <- get_dar( df )     #   Debt to Asset Ratio
df <- get_der( df )     #   Debt to Equity Ratio
df <- get_dgdr( df )    #   Donation/Grant Dependence Ratio
df <- get_dmr( df )     #   Debt Management Ratio
df <- get_doch( df )    #   Days of Operating Cash on Hand
df <- get_doci( df )    #   Days of Operating Cash and Investments
df <- get_eidr( df )    #   Earned Income Dependence Ratio
df <- get_er( df )      #   Equity Ratio
df <- get_ggr( df )     #   Government Grants Ratio
df <- get_iidr( df )    #   Investment Income Dependence Ratio
df <- get_lar( df )     #   Lands to Assets Ratio
df <- get_moch( df )    #   Months of Operating Cash on Hand
df <- get_or( df )      #   Operating Margin
df <- get_per( df )     #   Program Efficiency Ratio
df <- get_podpm( df )   #   Post-Depreciation Profitability Margin
df <- get_predpm( df )  #   Pre-Depreciation Profitability Margin
df <- get_qr( df )      #   Quick Ratio
df <- get_ssr( df )     #   Self Sufficiency Ratio
df <- get_stdr( df )    #   Short Term Debt Ratio

We will add the post-depreciation profitability margin and the quick ratio to the data:

df <- get_podpm( df ) 
## [1] "Revenues cannot be equal to zero: 0 cases have been replaced with NA."
##      podpm             podpm.w            podpm.n           podpm.p  
##  Min.   :-0.50950   Min.   :-0.46216   Min.   :-3.3121   Min.   : 1  
##  1st Qu.:-0.07950   1st Qu.:-0.07950   1st Qu.:-0.4535   1st Qu.:25  
##  Median : 0.01107   Median : 0.01107   Median : 0.2230   Median :49  
##  Mean   :-0.01858   Mean   :-0.01879   Mean   : 0.0000   Mean   :49  
##  3rd Qu.: 0.05557   3rd Qu.: 0.05557   3rd Qu.: 0.5555   3rd Qu.:73  
##  Max.   : 0.38204   Max.   : 0.31425   Max.   : 2.4878   Max.   :97  
##  NA's   :10         NA's   :10         NA's   :10        NA's   :10

df <- get_dgdr( df )
## [1] "Net assets cannot be zero: 0 cases have been replaced with NA."
##       dgdr             dgdr.w            dgdr.n            dgdr.p  
##  Min.   :0.04165   Min.   :0.04638   Min.   :-1.6821   Min.   : 1  
##  1st Qu.:0.42370   1st Qu.:0.42370   1st Qu.:-0.4077   1st Qu.:10  
##  Median :0.61054   Median :0.61054   Median : 0.2233   Median :19  
##  Mean   :0.54480   Mean   :0.54442   Mean   : 0.0000   Mean   :19  
##  3rd Qu.:0.80102   3rd Qu.:0.80102   3rd Qu.: 0.8667   3rd Qu.:28  
##  Max.   :0.92779   Max.   :0.90902   Max.   : 1.2314   Max.   :37  
##  NA's   :70        NA's   :70        NA's   :70        NA's   :70

For details on the definitions and calculation of the ratios try:

help( get_dgdr )
write.csv( df, "data/FINANCIALS-W-RATIOS.CSV", row.names=F )

Step 7: Add BMF Fields

The Business Master File contains important information not available on 990 forms such as organizational NTEE codes. In addition, the NCCS BMF files contain standardized geographies and other useful information.

The BMF rows for our sample have been precompiled:

root <- paste0( nodc, repo )
fn   <- "data/bmf_unified_10.csv"
bmf  <- read.csv( paste0( root, fn ) )
root <- paste0( nodc, repo )
fn   <- "data/FINANCIALS-W-RATIOS.CSV"
df   <- read.csv( paste0( root, fn )  )
df <- merge( df, bmf, by="EIN2" )
write.csv( df, "data/FINANCIALS-W-RATIOS-PLUS-BMF.CSV", row.names=F )

Step 8: Combine CEO Panel and Financials



Appendix

Step 5

The demo files above (a sample of 10 nonprofits), read directly from the demo repo on GitHub, were compiled using the following code:

EIN2_10 <- 
c("EIN-02-0240383", "EIN-03-0179298", 
  "EIN-04-2104310", "EIN-04-2259692", 
  "EIN-04-2592472", "EIN-04-2596491", 
  "EIN-04-3266589", "EIN-04-3543134", 
  "EIN-05-0258941", "EIN-06-0840436" )

tables <- 
c( "F9-P00-T00-HEADER",
"F9-P01-T00-SUMMARY",
"F9-P08-T00-REVENUE",
"F9-P09-T00-EXPENSES",
"F9-P10-T00-BALANCE-SHEET")

for( i in tables )
{
  for( j in 2009:2020 )
  { 
    df <- NULL
    try( df <- get_table( i, j ) )
    if( is.null(df) ){ next }
    sub <- dplyr::filter( df$EIN2 %in% EIN2_10 )
    fn <- paste0( i, "-", j, "-SAMPLE-10.CSV" )
    write.csv( sub, fn, row.names=F, na="" )
  }
}

# COMBINE ALL YEARS TO SINGLE FILE

root <- "https://raw.githubusercontent.com/Nonprofit-Open-Data-Collective/arnova-2024/refs/heads/main/data/"

for( i in tables )
{
  d.list <- list()
  for( j in 2009:2020 )
  {
    fn <- paste0( i, "-", j, "-SAMPLE-10.CSV" )
    url <- paste0( root, fn )
    df <- read.csv( url, colClasses = "character" ) 
    d.list[[ as.character(j) ]] <- df
  }
  dd <- dplyr::bind_rows( d.list )
  filename <- paste0( i, "-SAMPLE-10.CSV" )
  write.csv( dd,  filename, row.names=F, na="" )
}

# [1] "F9-P00-T00-HEADER-SAMPLE-10.CSV"       
# [2] "F9-P01-T00-SUMMARY-SAMPLE-10.CSV"      
# [3] "F9-P08-T00-REVENUE-SAMPLE-10.CSV"      
# [4] "F9-P09-T00-EXPENSES-SAMPLE-10.CSV"     
# [5] "F9-P10-T00-BALANCE-SHEET-SAMPLE-10.CSV"

For our toy example, we are particularly interested in the part of the 990 form that collects information about the board members in a nonprofit. This is known as Part VII data. We will begin by pulling two years of this data.

There are two main ways to access the data. You can download the data locally to your computer from the data catalog and read it into your R environment or you can use pre-built functions to read it directly in R. We present examples of both below.

# Reading in locally store data:
df2010 <- read.csv( "Coding/Data/PartVII/PartVII-2010.csv" )
df2011 <- read.csv( "Coding/Data/PartVII/PartVII-2011.csv" )

# Pull from NCCS using helper functions: 
df2010.fun <- get_partvii(2010)
df2011.fun <- get_partvii(2011)

Glass Cliff Analysis

There are several data cleaning and preparation steps that need to be performed on the base data before our analysis. These are beyond the scope of this toy example. Instead, we provide a cleaned subset of the data. We provide two dataframes of 1000 and 10 unique nonprofits that experienced at least one CEO transition between 2009 and 2019. It should be noted that it is possible for these organizations to experience more than one transition during this time frame. As such, we have a total of 1067 transitions. We can analyze various facets of these transitions.

ceo_trans_10    <- read.csv("toy_CEO_trans_10EIN.csv" )
ceo_trans_1000 <- read.csv("toy_CEO_trans_1000EIN.csv")

Financial Data Joining

The first thing we want to do with these data are to add financial information about the orgs during each respective year. We can again pull this info from the open data collective. This step takes about 10 minutes to run so we will comment out the lines and run similar steps with the data of 10 nonprofits and pre-subset financial dataframes.

summary <- read.csv("F9-P01-T00-SUMMARY-SAMPLE-10.csv")
revenue <- read.csv("F9-P08-T00-REVENUE-SAMPLE-10.csv")
expenses <- read.csv("F9-P09-T00-EXPENSES-SAMPLE-10.csv")


#let's join everything together:

ceo_trans_10_fncl <- ceo_trans_10 %>% left_join(summary, by = c("TAXYR" = "TAX_YEAR",
                                                               "EIN" = "ORG_EIN"))

ceo_trans_10_fncl <- ceo_trans_10_fncl %>% left_join(revenue, by = c("TAXYR" = "TAX_YEAR",
                                                               "EIN" = "ORG_EIN"))

ceo_trans_10_fncl <- ceo_trans_10_fncl %>% left_join(expenses, by = c("TAXYR" = "TAX_YEAR",
                                                               "EIN" = "ORG_EIN"))

write.csv(ceo_trans_10_fncl, "CEO_trans_10EIN_fncl.csv" )


# summary2009 <- get_table( "F9-P01-T00-SUMMARY", year=2009 )
# summary2010 <- get_table( "F9-P01-T00-SUMMARY", year=2010 )
# summary2011 <- get_table( "F9-P01-T00-SUMMARY", year=2011 )
# summary2012 <- get_table( "F9-P01-T00-SUMMARY", year=2012 )
# summary2013 <- get_table( "F9-P01-T00-SUMMARY", year=2013 )
# summary2014 <- get_table( "F9-P01-T00-SUMMARY", year=2014 )
# summary2015 <- get_table( "F9-P01-T00-SUMMARY", year=2015 )
# summary2016 <- get_table( "F9-P01-T00-SUMMARY", year=2016 )
# summary2017 <- get_table( "F9-P01-T00-SUMMARY", year=2017 )
# summary2018 <- get_table( "F9-P01-T00-SUMMARY", year=2018 )
# summary2019 <- get_table( "F9-P01-T00-SUMMARY", year=2019 )
# 
# revenue2009 <- get_table( "F9-P08-T00-REVENUE", year=2009 )
# revenue2010 <- get_table( "F9-P08-T00-REVENUE", year=2010 )
# revenue2011 <- get_table( "F9-P08-T00-REVENUE", year=2011 )
# revenue2012 <- get_table( "F9-P08-T00-REVENUE", year=2012 )
# revenue2013 <- get_table( "F9-P08-T00-REVENUE", year=2013 )
# revenue2014 <- get_table( "F9-P08-T00-REVENUE", year=2014 )
# revenue2015 <- get_table( "F9-P08-T00-REVENUE", year=2015 )
# revenue2016 <- get_table( "F9-P08-T00-REVENUE", year=2016 )
# revenue2017 <- get_table( "F9-P08-T00-REVENUE", year=2017 )
# revenue2018 <- get_table( "F9-P08-T00-REVENUE", year=2018 )
# revenue2019 <- get_table( "F9-P08-T00-REVENUE", year=2019 )
# 
# 
# expenses2009 <- get_table( "F9-P09-T00-EXPENSES", year=2009 )
# expenses2010 <- get_table( "F9-P09-T00-EXPENSES", year=2010 )
# expenses2011 <- get_table( "F9-P09-T00-EXPENSES", year=2011 )
# expenses2012 <- get_table( "F9-P09-T00-EXPENSES", year=2012 )
# expenses2013 <- get_table( "F9-P09-T00-EXPENSES", year=2013 )
# expenses2014 <- get_table( "F9-P09-T00-EXPENSES", year=2014 )
# expenses2015 <- get_table( "F9-P09-T00-EXPENSES", year=2015 )
# expenses2016 <- get_table( "F9-P09-T00-EXPENSES", year=2016 )
# expenses2017 <- get_table( "F9-P09-T00-EXPENSES", year=2017 )
# expenses2018 <- get_table( "F9-P09-T00-EXPENSES", year=2018 )
# expenses2019 <- get_table( "F9-P09-T00-EXPENSES", year=2019 )
# 
# 
# #Let's put them all together
# summary <- rbind(as.data.frame(summary2009), as.data.frame(summary2010),as.data.frame(summary2011),
#                  as.data.frame(summary2012),as.data.frame(summary2013),as.data.frame(summary2014),
#                  as.data.frame(summary2015),as.data.frame(summary2016),as.data.frame(summary2017),
#                  as.data.frame(summary2018),as.data.frame(summary2019))
# 
# revenue <- rbind(as.data.frame(revenue2009),as.data.frame(revenue2010),as.data.frame(revenue2011),
#                  as.data.frame(revenue2012),as.data.frame(revenue2013),as.data.frame(revenue2014),
#                  as.data.frame(revenue2015),as.data.frame(revenue2016),as.data.frame(revenue2017),
#                  as.data.frame(revenue2018),as.data.frame(revenue2019))
# 
# expenses <- rbind(as.data.frame(expenses2009),as.data.frame(expenses2010),as.data.frame(expenses2011),
#                   as.data.frame(expenses2012),as.data.frame(expenses2013),as.data.frame(expenses2014),
#                   as.data.frame(expenses2015),as.data.frame(expenses2016),as.data.frame(expenses2017),
#                   as.data.frame(expenses2018),as.data.frame(expenses2019))
# 
# 
# 
# 
# #Orgs can return various versions of their tax forms each year
# #For now, we will keep only the first observation, true analysis will need to revisit this decision
# summary <- summary %>% group_by(ORG_EIN, TAX_YEAR) %>%
#   filter(row_number()==1)
# 
# revenue <- revenue %>% group_by(ORG_EIN, TAX_YEAR) %>%
#   filter(row_number()==1)
# 
# expenses <- expenses %>% group_by(ORG_EIN, TAX_YEAR) %>%
#   filter(row_number()==1)
# 
# 
# 
# #Saving environment space by removing the other dfs
# remove(summary2009, summary2010, summary2011, summary2012, summary2013, summary2014,
#      summary2015, summary2016, summary2017, summary2018, summary2019 )
# remove(expenses2009, expenses2010, expenses2011, expenses2012, expenses2013, expenses2014,
#      expenses2015, expenses2016, expenses2017, expenses2018, expenses2019 )
# remove(balance2009, balance2010, balance2011, balance2012, balance2013, balance2014,
#      balance2015, balance2016, balance2017, balance2018, balance2019 )
# #Let's join everything together
# 
# 
# ceo_trans_1000_fncl <- ceo_trans_1000 %>% left_join(summary, by = c("TAXYR" = "TAX_YEAR",
#                                                               "EIN" = "ORG_EIN"))
# 
# ceo_trans_1000_fncl <- ceo_trans_1000_fncl %>% left_join(expenses, by = c("TAXYR" = "TAX_YEAR",
#                                                                    "EIN" = "ORG_EIN"))
# 
# ceo_trans_1000_fncl <- ceo_trans_1000_fncl %>% left_join(revenue, by = c("TAXYR" = "TAX_YEAR",
#                                                                    "EIN" = "ORG_EIN"))
# 
# 
# write.csv(ceo_trans_1000_fncl, "toy_CEO_trans_1000EIN_fncl.csv")

Summary Statistics of Transitions

Now we’re ready for some analyses! We are most interested in understanding transitions by their gendering. That is to say that we want to know if there are differences in observable firm dimensions for nonprofits that experience a CEO transition from male to male relative to the other permutations of male to female, female to male, and female to female. Let’s start by looking at org differences by these transition types. We will use the 1000 EIN observations for this analysis. We have provided this dataset with the financial data attached (this was accomplished by running the commented lines above). For simplicity, we will only consider a few firm dimensions such as employee size and total assets.

#This df 
ceo_trans_1000_fncl <- read.csv("toy_CEO_trans_1000EIN_fncl.csv")

#First need to denote transitions 
ceo_trans_1000_fncl <- ceo_trans_1000_fncl %>%
  arrange(EIN, transition_no, TAXYR)%>% #for each org, transition, and year, 
  group_by(EIN, transition_no) %>%       
  mutate(period = row_number())%>%       #let's denote which period the row is relative to (i.e., how many years before /after a transition)
  ungroup()

#Let's make this variable a bit more readable
ceo_trans_1000_fncl$period <- paste0("T", ceo_trans_1000_fncl$period-3)
ceo_trans_1000_fncl$period <- factor(ceo_trans_1000_fncl$period, levels = c("T-2", "T-1", "T0" , "T1", "T2"))


#Need to get transition type too
#We want to know if the transition is M>M, M>F, F>M, or F>M
#We will first create a string of the CEO gender throughout the 5 year period
#The we will create a column that keeps the first and last gender (this is the gender transition)
ceo_trans_1000_fncl<- ceo_trans_1000_fncl %>%
  group_by(EIN, transition_no)%>%
  mutate(trans_tot = str_c(unlist(strsplit(paste(gender, collapse = ""), "")), collapse = "")
  ) %>%
  mutate(
    trans_type = str_sub(trans_tot, 1, 1) %>%
      str_c(str_sub(trans_tot, nchar(trans_tot), nchar(trans_tot)))  # Combine 1st and last letter
    
  )

#We want this variable to be a factor
ceo_trans_1000_fncl$trans_type <- as.factor(ceo_trans_1000_fncl$trans_type)



#We're going to get firm attributes by transition type
#Let's consider the attributes at t-1
#We'll consider basic variables like number of employees, total assets, etc.

#Note that further data cleaning is necessary for final presentation due to NA values input into some of these columns. For now, we will just remove the nas and continue with our analysis

temp <- ceo_trans_1000_fncl %>% group_by(trans_type)%>%
  filter(period == "T-1") %>%
  summarize(count = n(),
    num_emp_av =mean(F9_01_ACT_GVRN_EMPL_TOT, na.rm = T),
            num_emp_sd = sd(F9_01_ACT_GVRN_EMPL_TOT, na.rm = T),
            num_vol_av = mean(F9_01_ACT_GVRN_VOL_TOT, na.rm = T),
            num_vol_sd = sd(F9_01_ACT_GVRN_VOL_TOT, na.rm = T),
            total_exp_av = mean(F9_01_EXP_TOT_PY, na.rm = T),
            total_exp_sd = sd(F9_01_EXP_TOT_PY, na.rm = T),
            total_assets_av = mean(F9_01_NAFB_ASSET_TOT_BOY, na.rm = T),
            total_assets_sd = sd(F9_01_NAFB_ASSET_TOT_BOY, na.rm = T))

temp


There are 349 transitions from male to male (MM) transitions, 253 male to female transitions (MF), 160 female to male transitions (FM), and 305 female to female transitions (FF) in our dataset. FF orgs have the smallest number of employees, on average while MF have the highest. It should be noted that the standard deviation for number of employees is pretty high, suggesting a wide range of nonprofits. MM nonprofits have the highest total expeness on average and the highest total assets. Again, the SDs are wide, suggesting large variation across observations.

Financial Measures by Transitions

The Glass Cliff Phenomenon hypothesizes that women are chosen for positions of power when these positions are more precarious. One way to denote a precarious position is by the firm’s financial performance; poor financial performance suggests more precariousness. We acknowledge that financial performance is comprised of several different dimensions and it is sometimes hard to arrive at clean conclusions about “poorly performing nonprofits.” For demonstration purposes we will specifically focus on the financial performance metric of post-depreciation profitability margin (podpm). This is defined as an income measure that determines a firm’s profitability after incorporating non-cash expenses. Higher values of this metric are generally desirable because the indicate that an org is not lost its revenue to expenses. We will use the package fiscal from the Open Data Collective to calculate our variable of interest. The default parameters of the respective functions are already built for the 990 naming conventions so usage is pretty straight forward!

#Make sure to run the code chunk about to ensure your df_long_fncl has all the necessary variables 

#site: https://github.com/Nonprofit-Open-Data-Collective/fiscal/tree/main/R
ceo_trans_1000_fncl <- get_podpm(ceo_trans_1000_fncl)



#Let's plot these measures
plot_temp <- ceo_trans_1000_fncl %>%
  group_by(trans_type, period)%>%
  summarize(median_podpm =median(podpm, na.rm = T))


ggplot(data = plot_temp, aes(x = period, y = median_podpm, group = trans_type, color = trans_type)) + 
  geom_line(linewidth = 1.5)+ 
  geom_text_repel(aes(label = round(median_podpm,3)), size = 5, nudge_x = -0.07, nudge_y = 0.001, segment.size = 0, segment.color = NA)+
  theme_bw( ) + 
#  theme(text = element_text(size = 24))+
  scale_x_discrete(labels = c("T-2", "T-1", "Transition", "T+1", "T+2"))+
  labs(color = "Transition") +
  xlab("Period")+
  ylab("Median Post-Depreciation Profitability Margin") +
  ggtitle("Median Post-Depreciation Profitability Margin by Transition Type") +
  geom_vline(xintercept = 3, linetype = "dashed")



################################################
#Now doing density plots of these respective vars
################################################

#Let's compare the MM density to MF density in the t-1 period for the vats

ceo_trans_1000_fncl_MM <- ceo_trans_1000_fncl %>% filter(trans_type == "MM" &
                                             period == "T-1")
ceo_trans_1000_fncl_MF <- ceo_trans_1000_fncl %>% filter(trans_type == "MF"&
                                             period == "T-1")


ggplot() + 
  geom_density(data = ceo_trans_1000_fncl_MM,  aes(x = podpm, fill = "lightblue"), alpha = 0.5) +
  geom_density(data = ceo_trans_1000_fncl_MF,  aes(x = podpm, fill = "pink"), alpha = 0.5) +
  xlim(-1,1)+
  theme_bw()+
#  theme(text = element_text(size = 24))+
  scale_fill_manual(name = "Transition", values = c('lightblue', 'pink'), labels = c("pink" = "MF" ,  "lightblue" = "MM") ) +
  xlab("Post-Depreciation Profitability Margin")+
  ylab("Density") +
  ggtitle("Density ofPost-Depreciation Profitability Margin by Transition at t-1")

The glass cliff phenomenon suggest that financial precarious NPs (seen by a significant drop in their financials between T-2 and T-1) would more likely hire a female to the CEO position. We start by looking at the PODPM variable over the periods. MM organizations start with the highest median profitability margin but also experience the steepest drop between T-2 and T-1. We see a similar slope for FM organizations although the starting point is the lowest in the entire group. MF transitions do not appear to be preceded by steep changes in profitability. If the glass cliff hypothesis were true, we would expect the observed trajectory of FM but would not expect the other firms to have similar metrics around their transitions. This graph calls for further analysis on other financial measures to determine whether FM firms display a transition during significantly more precarious times than their counterparts.



As a final understanding of the data, we look at the distribution of PODPM at T-1. We consider the two most relevant groups of MM and MF. We see that the distribution of financial variables tends to be extremely similar to both types of transitions, again failing to provide strong motivation for a glass cliff phenomenon.